package storage

import (
	"bufio"
	"database/sql"
	"fmt"
	"log"
	"os"
	"reflect"
	"strings"
)

// Article 定义可以存储的类型
type Article struct {
	Title    string
	Keywords string
	Creator  string
	Content  string
}

// Keywords 定义文章包含的关键词，后面一个是为了可以使用api的包装
type Keywords struct {
	Words []string
}
type Keyword struct {
	Word string
}

// ArticleKeyword 关联关键字和文章
type ArticleKeyword struct {
	ArticleID int64
	KeywordID int64
}

// 明文数据存储，后面升级为加密数据存储

// const sqlCommon = "hufeng_review"

func ExecuteSql(file string, db *sql.DB) error {
	// 进行sql文件的语句执行
	open, err := os.Open(file)
	if err != nil {
		return err
	}
	defer func() {
		if err = open.Close(); err != nil {
			err = fmt.Errorf("error on open sql file %v", err)
			fmt.Println(err)
			return
		}
	}()
	// 不执行导出到缓存，而是进行读取操作，设置一个string的builder
	scanner := bufio.NewScanner(open)
	var statement strings.Builder
	for scanner.Scan() {
		line := scanner.Text()
		if line == "" || strings.HasPrefix(line, "--") {
			// 去除包含前缀和注释的部分
			continue
		}
		statement.WriteString(line)
		// fmt.Println(statement.String())
		if strings.HasSuffix(line, ";") {
			_, err := db.Exec(statement.String())
			if err != nil {
				return err
			}
			// fmt.Println(statement.String())
			statement.Reset()
			// aI, err := res.RowsAffected()
			// iI, err := res.LastInsertId()
			//if err != nil {
			//	return err
			//}
			//fmt.Println(aI, iI)
		}
	}
	return nil
}

func CreateTable(statement string, db *sql.DB) error {
	_, err := db.Exec(statement)
	if err != nil {
		return err
	}
	return nil
}

// AddTableLine 思路：接受一个客户端的表名、任意类型的消息。连接数据库，查询表的所有行、列
func AddTableLine(table string, msg any) error {
	db, err := sql.Open("sqlite3", dataSource)
	if err != nil {
		return fmt.Errorf("error on open sqlite : %v", err)
	}
	defer func() {
		if err = db.Close(); err != nil {
			log.Println(err)
			return
		}
	}()
	rows, err := db.Query(fmt.Sprintf("SELECT * FROM %s", table))
	if err != nil {
		return fmt.Errorf("error on db query : %v", err)
	}
	columns, err := rows.Columns()
	if err != nil {
		return fmt.Errorf("error on rows-column : %v", err)
	}
	msgSlice := GetMsg(msg)
	stmt, err := db.Prepare(fmt.Sprintf("INSERT INTO %s %s VALUES %s", table, ChangeIntoString(columns, len(msgSlice)), GetRuneNum(len(msgSlice)))) // 这还是一个多步骤执行注入的方法
	if err != nil {
		return fmt.Errorf("error on prepare : %v", err)
	}
	defer func() {
		if err = stmt.Close(); err != nil {
			log.Println(err)
			return
		}
	}()
	_, err = stmt.Exec(msgSlice...)
	if err != nil {
		return fmt.Errorf("error on exec : %v", err)
	}
	fmt.Printf("%s: %v", "success", msg)
	return nil
	//fmt.Println(msgSlice)
	//fmt.Println(table, ChangeIntoString(columns, len(msgSlice)), GetRuneNum(len(msgSlice)), len(msgSlice))
	//fmt.Println(fmt.Sprintf("INSERT INTO %s %s VALUES %s", table, ChangeIntoString(columns, len(msgSlice)), GetRuneNum(len(msgSlice))))
	//_, err = stmt.Exec(msgN.id, msgN.name, msgN.usage)
	// 将外部数据内部化
	//fmt.Println(msgSlice...)
}
func ChangeIntoString(ss []string, n int) string {
	s := "("
	for i, x := range ss {
		if i == 0 {
			continue
		}
		if i == n+1 {
			break
		}
		s += fmt.Sprintf("%s,", x)
	}
	s = s[:len(s)-1]
	s += ")"
	return s
}
func GetRuneNum(x int) string {
	s := "("
	for i := 1; i <= x; i++ {
		s += "?,"
	}
	s = s[:len(s)-1]
	s += ")"
	return s
}
func GetMsg(msg any) []any {
	// 采用反射进行处理结构体的内容
	value := reflect.ValueOf(msg)
	if value.Kind() == reflect.Ptr {
		value = value.Elem()
	}
	//fmt.Println(value)
	if value.Kind() != reflect.Struct {
		log.Println("input type is not struct")
		return nil
	}
	res := make([]any, 0) // tips：这里创建一个slice，一定是长度为0
	for i := 0; i < value.NumField(); i++ {
		val := value.Field(i)
		actualVal := val.Interface()
		res = append(res, actualVal)
	}
	return res
}

// 明明就是可以加一句话就可说明白操作是怎么一回事，这些个文章偏偏不说明白

func UpdateTableLine(table string, msgN *msg) error {
	db, err := sql.Open("sqlite3", dataSource)
	if err != nil {
		log.Println(err)
		return err
	}
	fmt.Println("success link to sqlite")
	defer func() {
		if err = db.Close(); err != nil {
			log.Println(err)
			return
		}
	}()
	stmt, err := db.Prepare(fmt.Sprintf("UPDATE %s SET USAGE = ? WHERE ID = ?", table))
	if err != nil {
		return err
	}
	defer func() {
		if err = stmt.Close(); err != nil {
			log.Println(err)
			return
		}
	}()
	_, err = stmt.Exec(msgN.usage, msgN.id)
	if err != nil {
		return err
	}
	return nil
}

// 我们执行这些操作一般都是有固定输入和输出，这需要对大量项目进行总结 tag:2025-1-8

func GetTableLink(table string) ([]map[string]any, error) {
	db, err := sql.Open("sqlite3", dataSource)
	if err != nil {
		log.Println(err)
		return nil, err
	}
	defer func() {
		if err = db.Close(); err != nil {
			log.Println(err)
			return
		}
	}()
	rows, err := db.Query(fmt.Sprintf("SELECT * FROM %s", table))
	if err != nil {
		log.Println("error on get from sqlite, ", err)
		return nil, err
	}
	// 就TM这里，对于查询很JB烦，当初就是在这框框撞墙
	defer func() {
		if err = rows.Close(); err != nil {
			log.Println("error on close rows")
			return
		}
	}()
	// 还要对database/sql标准库进行系统性的学习
	columns, err := rows.Columns()
	if err != nil {
		return nil, err
	}
	//fmt.Println(columns)
	tableRows := make([]map[string]any, 0)
	for rows.Next() {
		row := make(map[string]any)
		value := make([]any, len(columns))
		valuePtr := make([]any, len(columns))
		for i := 0; i < len(columns); i++ {
			valuePtr[i] = &value[i]
		}
		if err = rows.Scan(valuePtr...); err != nil {
			log.Println(err)
			return nil, err
		}
		// fmt.Println(value, valuePtr)
		// 进行局部变量全局化
		for i, col := range columns {
			var v any
			val := value[i]
			bytes, ok := val.([]byte)
			if !ok {
				v = val
			} else {
				v = string(bytes)
			}
			row[col] = v
		}
		tableRows = append(tableRows, row)

		/* 下面的是基于一张特定表进行查询，前面是对于不同的表都可以进行查询
		//id, name, usage := 0, "", ""
		//if err := rows.Scan(&id, &name, &usage); err != nil {
		//	err := fmt.Errorf("error on get from rows %v", err)
		//	fmt.Println(err.Error())
		//	return
		//}
		//fmt.Printf("read from remote. id: %d,name: %s,usage: %s", id, name, usage)
		*/
	}
	//fmt.Printf("success to get %v", tableRows)
	// 最后这个rows还有一个error检查
	if err = rows.Err(); err != nil {
		log.Println(err)
		return nil, err
	}
	return tableRows, nil
}

func DeleteTableLine(table string, id int, field string) error {
	db, err := sql.Open("sqlite3", dataSource)
	if err != nil {
		log.Println(err)
		return err
	}
	defer func() {
		if err = db.Close(); err != nil {
			log.Println(err)
			return
		}
	}()
	stmt, err := db.Prepare(fmt.Sprintf("DELETE FROM %s WHERE %s = ?", table, field))
	if err != nil {
		return err
	}
	defer func() {
		if err = stmt.Close(); err != nil {
			log.Printf("error on close stmt : %v", err)
			return
		}
	}()
	res, err := stmt.Exec(id)
	if err != nil {
		return err
	}
	// 这里还可以像rabbitMq一样进行删除后的数据读取
	affected, err := res.RowsAffected()
	if err != nil {
		return err
	}
	fmt.Printf("delete from table and res is : %d", affected)
	return nil
}

type Scanner interface {
	Scan(rows *sql.Rows) error
}

type hufengStruct struct {
	id    int
	name  string
	usage string
}

func (hs *hufengStruct) Scan(rows *sql.Rows) error {
	return rows.Scan(&hs.id, &hs.name, &hs.usage) // 后续存在类似的表格问题使用这种方式进行处理
}

func GetFromTablePub[T Scanner](table string, db *sql.DB) {
	// 这里使用泛型针对不同的表进行处理
	rows, err := db.Query(fmt.Sprintf("SELECT * FROM %s", table))
	if err != nil {
		log.Println(fmt.Errorf("error on get from sqlite: %v", err))
		return
	}
	defer func() {
		if err = rows.Close(); err != nil {
			log.Println("error on close rows")
			return
		}
	}()
	var item T
	//item := reflect.New(reflect.TypeOf(data)).Interface().(Scanner)
	for rows.Next() {
		if err = item.Scan(rows); err != nil {
			log.Println(err)
			return
		}
	}
	fmt.Printf("msg : %v", item)
	if err = rows.Err(); err != nil {
		log.Println(err)
		return
	}
}
